package ca.etsmtl.logti.log619.lab05.utilities;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.enigmastation.classifier.CategoryIncrement;
import com.enigmastation.classifier.Classifier;
import com.enigmastation.classifier.ClassifierListener;
import com.enigmastation.classifier.ClassifierMap;
import com.enigmastation.classifier.FeatureIncrement;

public class DBListener implements ClassifierListener {
	{
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			Runtime.getRuntime().addShutdownHook(new Thread() {
				@Override
				public void run() {
					Connection conn = null;
					try {
						conn = getConnection();
						synchronized (conn) {
							if (conn != null) {
								PreparedStatement ps = conn.prepareStatement("shutdown");
								if (ps != null) {
									ps.execute();
									conn.close();
								}
							}
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			});
			/*Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement(
            "drop table cats");
            ps.execute();
            ps = conn.prepareStatement(
                    "create table cats(category varchar primary key, counts integer)");
            ps.execute();
            ps = conn.prepareStatement(
            "drop table feats");
            ps.execute();
            ps = conn.prepareStatement(
                    "create table feats(feature varchar, category varchar, "+
                            "counts integer, primary key(feature, category))");
            ps.execute();
            conn.close();*/
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		}
	}

	public void load(Classifier classifier) {
		try {
			Connection conn=getConnection();
			PreparedStatement ps=conn.prepareStatement("select category, counts from cats");
			ResultSet rs=ps.executeQuery();
			while(rs.next()) {
				String cat=rs.getString(1);
				int count=rs.getInt(2);
				classifier.getCategoryDocCount().put(cat, count);
				classifier.addCategory(cat);
			}
			ps=conn.prepareStatement("select category, feature, counts from feats");
			rs=ps.executeQuery();
			while(rs.next()) {
				String cat=rs.getString(1);
				String feat=rs.getString(2);
				int count=rs.getInt(3);
				ClassifierMap cm=classifier.getCategoryFeatureMap().getFeature(feat);
				cm.put(cat, count);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:hsqldb:file:testdb", "sa", "");
	}

	public void handleFeatureUpdate(FeatureIncrement featureIncrement) {
		try {
			Connection conn=getConnection();
			PreparedStatement ps=conn.prepareStatement(
					"update feats set counts=? where feature=? and category=?");
			ps.setInt(1, featureIncrement.getCount());
			ps.setString(2, featureIncrement.getFeature());
			ps.setString(3, featureIncrement.getCategory());
			int updates=ps.executeUpdate();
			if(updates==0) {
				ps=conn.prepareStatement(
						"insert into feats (counts, feature, category) values (?,?,?)");
				ps.setInt(1, featureIncrement.getCount());
				ps.setString(2, featureIncrement.getFeature());
				ps.setString(3, featureIncrement.getCategory());
				ps.executeUpdate();
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void handleCategoryUpdate(CategoryIncrement categoryIncrement) {
		try {
			Connection conn=getConnection();
			PreparedStatement ps=conn.prepareStatement(
					"update cats set counts=? where category=?");
			ps.setInt(1, categoryIncrement.getCount());
			ps.setString(2, categoryIncrement.getCategory());
			int updates=ps.executeUpdate();
			if(updates==0) {
				ps=conn.prepareStatement(
						"insert into cats (counts, category) values (?,?)");
				ps.setInt(1, categoryIncrement.getCount());
				ps.setString(2, categoryIncrement.getCategory());
				ps.executeUpdate();
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}